package ranking;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.Hashtable;
import java.util.Map;

import vo.User;
import JDBC.DatabaseDao;
import Service.CommentService;
import Utils.Constants;
import Utils.DateUtils;
import Utils.MathUtils;
import Utils.SortUtils;
import dao.StatusDao;
import dao.UserDao;
import dao.UserRelationDao;

public class GeneralRanking {
	/**
	 * @author 韩 闻文 user ranking by follower count
	 * @param topNumber
	 * @return
	 */
	public static ArrayList<User> getUserRankingByFollowersCount(int topNumber) {
		ArrayList<User> users = new ArrayList<User>();
		try {
			String sql = "select top " + topNumber + " * from users order by followers_count desc";
			System.out.println(sql);
			Statement stmt = DatabaseDao.getConnection().createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String userId = rs.getString("user_id");
				User user = UserDao.getById(userId);
				users.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return users;
	}

	/**
	 * @author 韩 闻文 user ranking by fav count
	 * @param topNumber
	 * @return
	 */
	public static ArrayList<User> getUserRankingByFavouritesCount(int topNumber) {
		ArrayList<User> users = new ArrayList<User>();
		try {
			String sql = "select top " + topNumber + " * from users order by favourites_count desc";
			System.out.println(sql);
			Statement stmt = DatabaseDao.getConnection().createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String userId = rs.getString("user_id");
				User user = UserDao.getById(userId);
				users.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return users;
	}

	/**
	 * @author 韩 闻文 user ranking by following count
	 * @param topNumber
	 * @return
	 */
	public static ArrayList<User> getUserRankingByFriendsCount(int topNumber) {
		ArrayList<User> users = new ArrayList<User>();
		try {
			String sql = "select top " + topNumber + " * from users order by friends_count desc";
			System.out.println(sql);
			Statement stmt = DatabaseDao.getConnection().createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String userId = rs.getString("user_id");
				User user = UserDao.getById(userId);
				users.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return users;
	}

	/**
	 * @author 韩 闻文 user ranking by tag count
	 * @param topNumber
	 * @return
	 */
	public static String getUserRankingByTagCount(int topNumber) {
		String result = "tag count,user id\n";
		try {
			String sql = "select top " + topNumber + " COUNT(*),USER_ID from user_tag group by USER_ID order by COUNT(*) desc";
			System.out.println(sql);
			Statement stmt = DatabaseDao.getConnection().createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String tag_count = rs.getString(1);// tag count
				String user_id = rs.getString(2);// user_id
				result += tag_count + "," + user_id + "\n";
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 user ranking by average status interval
	 * @return
	 */
	public static String getUserRankingByAVGStatusInterval(int topNumber) {
		String result = "";
		Hashtable<String, Double> userId_averageStatusInterval = new Hashtable<String, Double>();
		try {
			String sql = "select  * from users";
			Statement stmt = DatabaseDao.getConnection().createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String userId = rs.getString("user_id");
				ArrayList<Date> dateTimes = StatusDao.getStatusTimesByUserId(userId);
				// 只统计发帖数是大于阈值的
				if (dateTimes.size() > Constants.max_status_count) {
					ArrayList<Integer> intervals = DateUtils.getIntervals(dateTimes);
					double averageInterval = MathUtils.getAverage(intervals);
					userId_averageStatusInterval.put(userId, averageInterval);
				}
			}

			Map.Entry[] entries = SortUtils.sortedHashtableByValue(userId_averageStatusInterval, Constants.DESC);

			for (int i = 0; i < topNumber; i++)
				result += entries[i].getKey() + ":" + entries[i].getValue() + "\n";

		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 user ranking by average comment interval
	 * @return
	 */
	public static String getUserRankingByAVGCommentInterval(int topNumber) {
		String result = "";
		Hashtable<String, Double> userId_averageCommentInterval = new Hashtable<String, Double>();
		try {
			String sql = "select  * from users";
			Statement stmt = DatabaseDao.getConnection().createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String userId = rs.getString("user_id");
				ArrayList<Date> dateTimes = CommentService.getCommentTimesByUserId(userId);
				// 只统计评论数是大于阈值的
				if (dateTimes.size() > Constants.max_comment_count) {
					ArrayList<Integer> intervals = DateUtils.getIntervals(dateTimes);
					double averageInterval = MathUtils.getAverage(intervals);
					userId_averageCommentInterval.put(userId, averageInterval);
				}
			}
			Map.Entry[] entries = SortUtils.sortedHashtableByValue(userId_averageCommentInterval, Constants.DESC);
			for (int i = 0; i < topNumber; i++)
				result += entries[i].getKey() + ":" + entries[i].getValue() + "\n";

		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 user ranking by two-direction relation count
	 * @return
	 */
	public static String getUserRankingByReciprocitiesCount(int topNumber) {
		String result = "";
		Hashtable<String, Integer> userId_reciprocityCount = new Hashtable<String, Integer>();
		try {
			String sql = "select  * from users";
			Statement stmt = DatabaseDao.getConnection().createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String userId = rs.getString("user_id");
				int reciprocities = UserRelationDao.getReciprocityCountByUserId(userId);
				// don't sort when no relations existe
				if (reciprocities != 0)
					userId_reciprocityCount.put(userId, reciprocities);
			}
			Map.Entry[] entries = SortUtils.sortedHashtableByValue(userId_reciprocityCount, Constants.DESC);
			if (entries.length < topNumber)
				topNumber = entries.length;
			for (int i = 0; i < topNumber; i++)
				result += entries[i].getKey() + ":" + entries[i].getValue() + "\n";
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 user ranking by status count
	 * 
	 * @param topNumber
	 * @return
	 */
	public static ArrayList<User> getUserRankingByStatusesCount(int topNumber) {
		ArrayList<User> users = new ArrayList<User>();
		try {
			String sql = "select top " + topNumber + " * from users order by statuses_count desc";
			System.out.println(sql);
			Statement stmt = DatabaseDao.getConnection().createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String userId = rs.getString("user_id");
				User user = UserDao.getById(userId);
				users.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return users;
	}

	/**
	 * @author 韩 闻文 user ranking by status count
	 * 
	 * @param topNumber
	 * @return
	 */
	public static ArrayList<User> getUserRankingByCommentsCount(int topNumber) {
		ArrayList<User> users = new ArrayList<User>();
		try {
			String sql = "select top " + topNumber + " user_id,COUNT(*) from comments group by user_id order by COUNT(*) desc";
			System.out.println(sql);
			Statement stmt = DatabaseDao.getConnection().createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String userId = rs.getString("user_id");
				User user = UserDao.getById(userId);
				users.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return users;
	}

	public static String userRanking2String(ArrayList<User> users) {
		String result = "";
		for (int i = 0; i < users.size(); i++) {
			User user = users.get(i);
			result += user.getName() + "," + user.getFollowsCount() + "," + user.getFriendCount() + "," + user.getStatusesCount() + "\n";
		}
		return result;
	}

	public static void main(String args[]) {
		System.out.println(userRanking2String(GeneralRanking.getUserRankingByStatusesCount(10)));

		System.out.println(userRanking2String(GeneralRanking.getUserRankingByCommentsCount(10)));
		System.out.println(userRanking2String(GeneralRanking.getUserRankingByFollowersCount(10)));
		System.out.println(userRanking2String(GeneralRanking.getUserRankingByFriendsCount(10)));
	}
}
